import mysql.connector
from datetime import datetime, timedelta
import time

# 数据库配置
db_config = {
    "host": "192.168.3.167",
    "user": "tester",
    "password": "tester1234",
    "database": "gwza231dev",
}

# 监控配置
MONITOR_DEVICES = (
    "GW534D","GW537A","GW4526","GW58A1","GW1869","GW56DC","GW5110","GW5318","GW1D89",
    "GW4920","GW520A","GW1686","GW1D59","GW52AD","GW568B","GW589C","GW5054","GW493A",
    "GW56A7","GW4704","GW5691","GW4EA2","GW5416","GW44F1","GW1746","GW5696","GW5686",
    "GW46AF","GW5014","GW579E","GW5809","GW52A8","GW1829","GW1605",'GW15D0','GW5297',
    'GW4E9D','GW5502','GW15A0','GW533A','GW488D','GW42A8','GW522B','GW179E','GW58B2'
)
# MONITOR_DEVICES = (
#     "GW1829"
# )
CHECK_INTERVAL = 10  # 检查间隔（秒）
ALERT_THRESHOLD = 10  # 报警阈值
TIME_WINDOW = 60  # 时间窗口（秒）

# 报警记录字典（设备ID: 最后报警时间）
alert_history = {}

def get_connection():
    return mysql.connector.connect(**db_config)

def check_devices(table_name, device_ids):
    try:
        # 验证表名有效性（防止SQL注入）
        if not table_name.startswith("map_device_his"):
            raise ValueError("Invalid table name")
            
        conn = get_connection()
        cursor = conn.cursor()

        # 生成动态SQL
        placeholders = ', '.join(['%s'] * len(device_ids))
        query = f"""
        SELECT 
            CAST(device_id AS CHAR) AS device_id, 
            COUNT(*) AS record_count
        FROM {table_name}
        WHERE device_id IN ({placeholders})
            AND update_time >= NOW() - INTERVAL %s SECOND
        GROUP BY device_id
        HAVING record_count >= %s
        """
        
        # 构造参数列表：设备IDs + 时间窗口 + 阈值
        params = list(device_ids) + [TIME_WINDOW, ALERT_THRESHOLD]

        cursor.execute(query, params)
        current_time = datetime.now()

        for device_id, count in cursor:
            # 检查上次报警时间
            last_alert = alert_history.get(device_id)

            # 报警逻辑（每分钟仅报一次）
            if (not last_alert) or ((current_time - last_alert).total_seconds() > 60):
                print(
                    f"[警报] 设备 {device_id} 在最近{TIME_WINDOW}秒内更新{count}次！"
                    f"（检测时间：{current_time.strftime('%Y-%m-%d %H:%M:%S')}）"
                )
                alert_history[device_id] = current_time

        cursor.close()
    except Exception as e:
        print(f"数据库操作失败: {e}")
    finally:
        if conn and conn.is_connected():
            conn.close()

if __name__ == "__main__":
    print(f"启动设备监控服务，监控设备：{MONITOR_DEVICES}")
    print(f"检查间隔：{CHECK_INTERVAL}秒，报警条件：{ALERT_THRESHOLD}次/{TIME_WINDOW}秒")
    
    # 验证历史表名称
    his_table = 'map_device_his0328'
    
    while True:
        check_devices(his_table, MONITOR_DEVICES)
        time.sleep(CHECK_INTERVAL)